package org.jeik.data.services;

import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import org.jeik.data.dao.DAO;
import org.jeik.data.dao.DAOFactory;
import org.jeik.data.dao.DaoRecord;
import org.jeik.data.dao.DataException;
import org.jeik.data.models.Attachment;
import org.jeik.data.models.Service;
import org.jeik.data.models.ServiceParent;
import org.jeik.utils.Log;


public class EmbassyServicesService extends ServiceBase {
	
	/**
	 * 
	 */
	private EmbassyServicesService(){
	}
	
	/**
	 * 
	 * @author AAsha
	 *
	 */
	private static class SingletonHolder{
		private static EmbassyServicesService instance = new EmbassyServicesService(); 
	}
	
	/**
	 * 
	 * @return EmbassyServicesService instance
	 */
	public static EmbassyServicesService getInstance(){
		return SingletonHolder.instance;
	}
	
	/**
	 * 
	 * @return
	 * @throws DataException
	 */
	public List<ServiceParent> loadEmbassyServicesParents() throws DataException{
		
		DAO dao1 = DAOFactory.getInstance().createDao();

		String sql = "SELECT * FROM " + Tables.service_parents.name();
		dao1.execute(sql);

		
		List<ServiceParent> services = new ArrayList<ServiceParent>();
		ServiceParent serviceParent = null;
		Service service = null;
		Attachment attachment = null;
		
		DAO dao2 = DAOFactory.getInstance().createDao();
		DAO dao3 = DAOFactory.getInstance().createDao();
		DAO dao4 = DAOFactory.getInstance().createDao();
		
		for(DaoRecord record1:dao1.getResult()){
			serviceParent = new ServiceParent();
			serviceParent.setId((Long) record1.getValue(Columns.ID.name()));
			serviceParent.setServiceTitle(record1.getValue(Columns.TITLE.name()).toString());
			serviceParent.setType((Long) record1.getValue(Columns.TYPE.name()));
			serviceParent.setIndex(record1.getValue(Columns.INDX.name()));
			
			String fetchJoinId = "SELECT "+ Columns.SERVICE_ID.name() +" FROM "+ 
					Tables.services_parents_jt.name() +" WHERE "+  Columns.PARENT_ID.name() +" = "+ serviceParent.getId() +" ORDER BY INDX";
			StringBuilder ids = new StringBuilder();
			dao4.execute(fetchJoinId);
			
			for(int i=0; i<dao4.getResult().size(); i++){
				
				ids.append(dao4.getResult().get(i).getValue(0).toString());
				if((i+1) < dao4.getResult().size()){
					ids.append(",");
				}
			}
			
			
			sql = "SELECT * FROM " + Tables.embassy_services.name()+ 
					" WHERE ID IN ("+ ids +") ORDER BY FIELD(ID, "+ ids +")";
			dao2.execute(sql);
			for(DaoRecord record2:dao2.getResult()){
				service = new Service();
				service.setId((Long) record2.getValue(Columns.ID.name()));
				service.setTitle(record2.getValue(Columns.TITLE.name()).toString());
				service.setBody(record2.getValue(Columns.BODY.name()).toString());
				
				sql = "SELECT * FROM " + Tables.attachments.name() + 
						" WHERE ID IN (SELECT "+ Columns.ATTACHMENT_ID.name() +" FROM "+ 
						Tables.service_attachments.name() +" WHERE "+  Columns.SERVICE_ID.name() +" = "+ service.getId() +")";
				dao3.execute(sql);
				for(DaoRecord record3:dao3.getResult()){
					attachment = new Attachment();
					attachment.setId((Long) record3.getValue(Columns.ID.name()));
					attachment.setAttachmentFileName(record3.getValue(Columns.NAME.name()).toString());
					attachment.setAttachmentTitle(record3.getValue(Columns.TITLE.name()).toString());
					
					service.getAttachments().add(attachment);
				}
				
				serviceParent.getRequests().add(service);
				
			}
			
			services.add(serviceParent);
		}
		
		Collections.sort(services);
		
		return services;
	}
	
	/**
	 * 
	 * @return list of services.
	 * @throws DataException 
	 */
	public List<Service> loadEmbassyServices() throws DataException {
		DAO dao1 = DAOFactory.getInstance().createDao();

		String sql = "SELECT * FROM " + Tables.embassy_services.name();
		dao1.execute(sql);
		
		List<Service> services = new ArrayList<Service>();
		Service service = null;
		Attachment attachment = null;
		
		DAO dao2 = DAOFactory.getInstance().createDao();
		
		for(DaoRecord record:dao1.getResult()){
			service = new Service();
			service.setId((Long) record.getValue(Columns.ID.name()));
			service.setTitle(record.getValue(Columns.TITLE.name()).toString());
			service.setBody(record.getValue(Columns.BODY.name()).toString());
			
			//Load Service Attachments
			sql = "SELECT * FROM " + Tables.attachments.name() + 
					" WHERE ID IN (SELECT "+ Columns.ATTACHMENT_ID.name() +" FROM "+ 
					Tables.service_attachments.name() +" WHERE "+  Columns.SERVICE_ID.name() +" = "+ service.getId() +")";
			dao2.execute(sql);
			for(DaoRecord record2:dao2.getResult()){
				attachment = new Attachment();
				attachment.setId((Long) record2.getValue(Columns.ID.name()));
				attachment.setAttachmentFileName(record2.getValue(Columns.NAME.name()).toString());
				attachment.setAttachmentTitle(record2.getValue(Columns.TITLE.name()).toString());
				
				service.getAttachments().add(attachment);
			}
			
			services.add(service);
		}
		
		return services;
	}
	
	/**
	 * 
	 * @return list of attachments.
	 * @throws DataException 
	 */
	public List<Attachment> loadAttachments() throws DataException {
		DAO dao = DAOFactory.getInstance().createDao();
		
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT * FROM " + Tables.attachments.name());
		dao.execute(sb.toString());
		
		List<Attachment> attachments = new ArrayList<Attachment>();
		Attachment attachment = null;
		
		for(DaoRecord record:dao.getResult()){
			attachment = new Attachment();
			attachment.setId((Long) record.getValue(Columns.ID.name()));
			attachment.setAttachmentTitle(record.getValue(Columns.TITLE.name()).toString());
			attachment.setAttachmentFileName(record.getValue(Columns.NAME.name()).toString());
			
			attachments.add(attachment);
		}
		
		
		return attachments;
	}
	
	
	public void updateServiceParentsOrder(List<ServiceParent> embassyParentServicesList) throws DataException {
		
		DAO dao = DAOFactory.getInstance().createDao();
		
		for(ServiceParent service:embassyParentServicesList){
			String sql = "UPDATE "+ Tables.service_parents.name() +" SET "+ Columns.INDX.name() +" ="+ service.getIndex() +" WHERE ID= "+ service.getId();
			dao.executeUpdate(sql);
		}
		
	}
	
	
	/**
	 * 
	 * Add Service parent and its sublist services (requests).
	 * 
	 * @param serviceParent
	 * @throws DataException
	 */
	public void addServiceParent(ServiceParent serviceParent) throws DataException {
		
		DAO dao = DAOFactory.getInstance().createDao();
		
		//Add Service Parent
		String sql = "INSERT INTO "+ Tables.service_parents.name() +" (TITLE, TYPE, INDX) VALUES ('"+ serviceParent.getServiceTitle() +"', 1, "+ serviceParent.getIndex() +")";
		dao.executeUpdate(sql);
		
		//Get Newly added service parent Id
		dao.execute("SELECT LAST_INSERT_ID()");
		DaoRecord record = dao.getResult().get(0);
		Object obj = record.getValue(0);
		if(obj == null){
			return;
		}
		
		//Add Service parent services or requests.
		serviceParent.setId(Long.valueOf(obj.toString()));
		for(int i=0; i<serviceParent.getRequests().size(); i++){
			sql = "INSERT INTO  "+ Tables.services_parents_jt.name() 
					+" (PARENT_ID, SERVICE_ID, INDX) VALUES ( "+ obj.toString() +" , "+ serviceParent.getRequests().get(i).getId() +" , " + i +" )";
			dao.executeUpdate(sql);
		}
		
	}
	
	
	/**
	 * 
	 * @param serviceParent
	 * @throws DataException
	 */
	public void editServiceParent(ServiceParent serviceParent) throws DataException{
		
		DAO dao = DAOFactory.getInstance().createDao();
		
		//Update Service Parent
		String sql = "UPDATE "+ Tables.service_parents.name() +" SET TITLE ='"+ serviceParent.getServiceTitle() +"' WHERE ID= "+ serviceParent.getId();

		dao.executeUpdate(sql);
		
		//Delete all Service parent services or requests. 
		sql ="DELETE FROM "+ Tables.services_parents_jt.name() +" WHERE PARENT_ID = "+ serviceParent.getId();

		dao.executeUpdate(sql);
		
		//Add Service parent services or requests.
		for(int i=0; i<serviceParent.getRequests().size(); i++){
			sql = "INSERT INTO  "+ Tables.services_parents_jt.name() 
					+" (PARENT_ID, SERVICE_ID, INDX) VALUES ( "+ serviceParent.getId() +" , "+ serviceParent.getRequests().get(i).getId() +" , " + i +" )";
			dao.executeUpdate(sql);
		}
		
	}
	
	/**
	 * 
	 * @param serviceParent
	 * @throws DataException
	 */
	public void deleteServiceParent(ServiceParent serviceParent) throws DataException{
		
		DAO dao = DAOFactory.getInstance().createDao();
		
		//Delete all Service parent services or requests. 
		String sql ="DELETE FROM "+ Tables.services_parents_jt.name() +" WHERE PARENT_ID = "+ serviceParent.getId();
		dao.executeUpdate(sql);
		
		//Update Service Parent
		sql = "DELETE FROM "+ Tables.service_parents.name() +" WHERE ID = "+ serviceParent.getId();
		dao.executeUpdate(sql);
	}
	
	//============================================
	//============================================
	//============================================
	
	/**
	 * 
	 * Add Service and its sublist attachments.
	 * 
	 * @param service
	 * @throws DataException
	 */
	public void addService(Service service) throws DataException {
		
		DAO dao = DAOFactory.getInstance().createDao();
		
		//Add Service
		String sql = "INSERT INTO "+ Tables.embassy_services.name() +" (TITLE, BODY) VALUES ('"+ service.getTitle() +"', '"+ service.getBody() +"')";
		dao.executeUpdate(sql);
		
		//Get Newly added service Id
		dao.execute("SELECT LAST_INSERT_ID()");
		DaoRecord record = dao.getResult().get(0);
		Object serviceId = record.getValue(0);
		if(serviceId == null){
			return;
		}
		service.setId(Long.valueOf(serviceId.toString()));
		
		//Add Service Attachments.
		for(Attachment attachment:service.getAttachments()){
			sql = "INSERT INTO "+ Tables.attachments.name() +" (TITLE, NAME) VALUES ('"+ attachment.getAttachmentTitle() +"', '"+ attachment.getAttachmentFileName() +"')";
			dao.executeUpdate(sql);
			
			//Get Newly added attachment Id
			dao.execute("SELECT LAST_INSERT_ID()");
			record = dao.getResult().get(0);
			Object attachmentId = record.getValue(0);
			if(attachmentId == null){
				return;
			}
			attachment.setId((Long) attachmentId);
			
			sql = "INSERT INTO  "+ Tables.service_attachments.name() +" (SERVICE_ID, ATTACHMENT_ID) VALUES ( "+ service.getId() +" , "+ attachment.getId() +" )";
			dao.executeUpdate(sql);
		}
		
	}
	
	
	/**
	 * 
	 * @param service
	 * @throws DataException
	 */
	public void editService(Service service) throws DataException{
		
		DAO dao = DAOFactory.getInstance().createDao();
		
		//Update Service
		String sql = "UPDATE "+ Tables.embassy_services.name() +" SET TITLE ='"+ service.getTitle() +"', BODY ='"+ service.getBody() +"' WHERE ID= "+ service.getId() ;
		dao.executeUpdate(sql);
				
		//Delete all Service attachments. 
		sql ="DELETE FROM "+ Tables.service_attachments.name() +" WHERE SERVICE_ID = "+ service.getId();
		dao.executeUpdate(sql);
				
		//Add Service attachments. 
		for(Attachment attachment:service.getAttachments()){
			
			sql ="DELETE FROM "+ Tables.attachments.name() +" WHERE ID = "+ attachment.getId();
			dao.executeUpdate(sql);
			
			Log.debug(this, "editService - delete from attachments ");
			
			sql = "INSERT INTO "+ Tables.attachments.name() +" (TITLE, NAME) VALUES ('"+ attachment.getAttachmentTitle() +"', '"+ attachment.getAttachmentFileName() +"')";
			dao.executeUpdate(sql);
			dao.execute("SELECT LAST_INSERT_ID()");
			Object attachmentId = dao.getResult().get(0).getValue(0);
			if(attachmentId == null){
				return;
			}
			attachment.setId(Long.valueOf(attachmentId.toString()));
			
			sql = "INSERT INTO  "+ Tables.service_attachments.name() +" (SERVICE_ID, ATTACHMENT_ID) VALUES ( "+ service.getId() +" , "+ attachment.getId() +" )";
			dao.executeUpdate(sql);
			
		}
		
	}
	
	/**
	 * 
	 * @param service
	 * @throws DataException
	 */
	public void deleteService(Service service) throws DataException{
		
		DAO dao = DAOFactory.getInstance().createDao();
		
		//Delete all Service Attachment. 
		String sql ="DELETE FROM "+ Tables.service_attachments.name() +" WHERE SERVICE_ID = "+ service.getId();
		dao.executeUpdate(sql);
		
		for(Attachment attachment:service.getAttachments()){
			sql ="DELETE FROM "+ Tables.attachments.name() +" WHERE ID = "+ attachment.getId();
			dao.executeUpdate(sql);
		}
		
		
		//Update Service
		sql = "DELETE FROM "+ Tables.embassy_services.name() +" WHERE ID = "+ service.getId();
		dao.executeUpdate(sql);
		
	}
	
}
